package com.scau.RBAC.dao.impl;

import com.scau.RBAC.dao.UserRoleDao;
import com.scau.RBAC.model.entity.UserRole;
import com.scau.RBAC.util.DBConnectionSingleton;
import com.scau.RBAC.util.DatabaseException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class MySqlUserRoleDao implements UserRoleDao {

    @Override
    public int insert(UserRole entity) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = DBConnectionSingleton.getInstance().getConnection();
            ps = conn.prepareStatement(
                    "INSERT INTO user_roles(user_id, role_id, created_time, created_by) VALUES(?, ?, ?, ?)");

            ps.setLong(1, entity.getUserId());
            ps.setLong(2, entity.getRoleId());
            ps.setObject(3, entity.getCreatedTime());
            ps.setLong(4, entity.getCreatedBy() != null ? entity.getCreatedBy() : 0);

            return ps.executeUpdate();
        } catch (SQLException e) {
            throw new DatabaseException("插入用户角色关联失败", e);
        } finally {
            closeResources(ps, null);
        }
    }

    @Override
    public int batchInsert(List<UserRole> entities) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = DBConnectionSingleton.getInstance().getConnection();
            conn.setAutoCommit(false);

            ps = conn.prepareStatement(
                    "INSERT INTO user_roles(user_id, role_id, created_time, created_by) VALUES(?, ?, ?, ?)");

            for (UserRole userRole : entities) {
                ps.setLong(1, userRole.getUserId());
                ps.setLong(2, userRole.getRoleId());
                ps.setObject(3, userRole.getCreatedTime());
                ps.setLong(4, userRole.getCreatedBy() != null ? userRole.getCreatedBy() : 0);
                ps.addBatch();
            }

            int[] results = ps.executeBatch();
            conn.commit();

            int total = 0;
            for (int result : results) {
                total += result;
            }
            return total;
        } catch (SQLException e) {
            try {
                if (conn != null) {
                    conn.rollback();
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            throw new DatabaseException("批量插入用户角色关联失败", e);
        } finally {
            try {
                if (conn != null) {
                    conn.setAutoCommit(true);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            closeResources(ps, null);
        }
    }

    @Override
    public int deleteById(Long id) {
        // UserRole表没有单独的id字段，使用userId和roleId组合主键
        // 这里简化实现，实际应该根据业务需求处理
        throw new UnsupportedOperationException("UserRole表没有单独的id字段，无法通过id删除");
    }

    @Override
    public int batchDelete(List<Long> ids) {
        // 与deleteById类似，UserRole表没有单独的id字段
        throw new UnsupportedOperationException("UserRole表没有单独的id字段，无法批量删除");
    }

    @Override
    public int update(UserRole entity) {
        // UserRole表通常不需要更新操作，因为它是关联表
        throw new UnsupportedOperationException("UserRole表不支持更新操作");
    }

    @Override
    public UserRole findById(Long id) {
        // UserRole表没有单独的id字段
        throw new UnsupportedOperationException("UserRole表没有单独的id字段，无法通过id查询");
    }

    @Override
    public List<UserRole> findAll() {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DBConnectionSingleton.getInstance().getConnection();
            ps = conn.prepareStatement("SELECT * FROM user_roles");
            rs = ps.executeQuery();

            List<UserRole> userRoles = new ArrayList<>();
            while (rs.next()) {
                userRoles.add(mapResultSetToUserRole(rs));
            }
            return userRoles;
        } catch (SQLException e) {
            throw new DatabaseException("查询所有用户角色关联失败", e);
        } finally {
            closeResources(ps, rs);
        }
    }

    @Override
    public List<UserRole> findByPage(Map<String, Object> params, int pageNum, int pageSize) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DBConnectionSingleton.getInstance().getConnection();
            StringBuilder sql = new StringBuilder("SELECT * FROM user_roles WHERE 1=1");
            List<Object> paramList = new ArrayList<>();

            // 构建查询条件
            buildQueryConditions(sql, params, paramList);

            // 添加分页
            sql.append(" LIMIT ? OFFSET ?");
            paramList.add(pageSize);
            paramList.add((pageNum - 1) * pageSize);

            ps = conn.prepareStatement(sql.toString());

            // 设置参数
            for (int i = 0; i < paramList.size(); i++) {
                ps.setObject(i + 1, paramList.get(i));
            }

            rs = ps.executeQuery();
            List<UserRole> userRoles = new ArrayList<>();
            while (rs.next()) {
                userRoles.add(mapResultSetToUserRole(rs));
            }
            return userRoles;
        } catch (SQLException e) {
            throw new DatabaseException("分页查询用户角色关联失败", e);
        } finally {
            closeResources(ps, rs);
        }
    }

    @Override
    public long count(Map<String, Object> params) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DBConnectionSingleton.getInstance().getConnection();
            StringBuilder sql = new StringBuilder("SELECT COUNT(*) FROM user_roles WHERE 1=1");
            List<Object> paramList = new ArrayList<>();

            // 构建查询条件
            buildQueryConditions(sql, params, paramList);

            ps = conn.prepareStatement(sql.toString());

            // 设置参数
            for (int i = 0; i < paramList.size(); i++) {
                ps.setObject(i + 1, paramList.get(i));
            }

            rs = ps.executeQuery();
            if (rs.next()) {
                return rs.getLong(1);
            }
            return 0;
        } catch (SQLException e) {
            throw new DatabaseException("统计用户角色关联数量失败", e);
        } finally {
            closeResources(ps, rs);
        }
    }

    @Override
    public int deleteByUserId(Long userId) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = DBConnectionSingleton.getInstance().getConnection();
            ps = conn.prepareStatement("DELETE FROM user_roles WHERE user_id = ?");
            ps.setLong(1, userId);
            return ps.executeUpdate();
        } catch (SQLException e) {
            throw new DatabaseException("根据用户ID删除用户角色关联失败", e);
        } finally {
            closeResources(ps, null);
        }
    }

    @Override
    public int deleteByRoleId(Long roleId) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = DBConnectionSingleton.getInstance().getConnection();
            ps = conn.prepareStatement("DELETE FROM user_roles WHERE role_id = ?");
            ps.setLong(1, roleId);
            return ps.executeUpdate();
        } catch (SQLException e) {
            throw new DatabaseException("根据角色ID删除用户角色关联失败", e);
        } finally {
            closeResources(ps, null);
        }
    }

    // 辅助方法：将ResultSet映射到UserRole对象
    private UserRole mapResultSetToUserRole(ResultSet rs) throws SQLException {
        UserRole userRole = new UserRole();
        userRole.setUserId(rs.getLong("user_id"));
        userRole.setRoleId(rs.getLong("role_id"));
        userRole.setCreatedTime(rs.getObject("created_time", LocalDateTime.class));
        userRole.setCreatedBy(rs.getLong("created_by"));
        return userRole;
    }

    // 辅助方法：构建查询条件
    private void buildQueryConditions(StringBuilder sql, Map<String, Object> params, List<Object> paramList) {
        if (params == null) {
            return;
        }

        if (params.containsKey("userId")) {
            sql.append(" AND user_id = ?");
            paramList.add(params.get("userId"));
        }

        if (params.containsKey("roleId")) {
            sql.append(" AND role_id = ?");
            paramList.add(params.get("roleId"));
        }
    }

    // 辅助方法：关闭资源
    private void closeResources(PreparedStatement ps, ResultSet rs) {
        try {
            if (rs != null) {
                rs.close();
            }
            if (ps != null) {
                ps.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}